'''
Date: 2022-04-26 13:45:13
LastEditors: jiangh
LastEditTime: 2022-04-27 14:19:37
FilePath: \biblical-reptile\sql.py
'''
from asyncio.windows_events import NULL
import pymysql

# 参考 https://blog.csdn.net/wenxuhonghe/article/details/121945942


# MySQL 关联两张表 常用的几种方法  https://blog.csdn.net/lilygg/article/details/114916481

db = pymysql.connect(host='localhost', port=3306, user='root',
                     passwd='root', db='shengjing', charset='utf8')
cursor = db.cursor()
# print(db.cursor())
# print(dir(db))


def queryDB():
    # 查询数据
    try:
        sql = "SELECT * FROM `user`"
        cursor.execute(sql)
        data = cursor.fetchall()
        print(data)
    except Exception as e:
        print(e)


def insertDB(id, name, password, register_time, phone):
    # 插入数据
    try:
        sql = "INSERT INTO `user` (`id`, `name`, `password`, `register_time`, `phone`)" + " VALUES ('%s', '%s', '%s', '%s', '%s')" % (
            id, name, password, register_time, phone)
        # print(sql)

        cursor.execute(sql)
        db.commit()
    except Exception as e:
        db.rollback()
        print(e)


# INSERT INTO `user` (`id`, `name`, `password`, `register_time`, `phone`) VALUES (NULL, '1', '11', '1', '1');
if __name__ == '__main__':
    queryDB()
    # id 使用mysql中的null 应为使用了mysql的自动设置id ,其他的使用null 在其他语言中进行判断
    insertDB(NULL, '江浩', 'jiangh', '22', None)

    # 关闭光标对象
    cursor.close()
    # 关闭数据库连接
    db.close()
